package com.suez.nullpointerexception.blizzard.impl;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.junit.Test;

import com.suez.nullpointerexception.blizzard.dao.DeptDao;
import com.suez.nullpointerexception.blizzard.entity.Dept;
import com.suez.nullpointerexception.blizzard.entity.Person;

public class DeptDaoImpl implements DeptDao{

	@Override
	public List<Dept> getAll() {
		String sql = "select * from DEPT";
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Dept> depts = new ArrayList<>();
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){				
				Dept d = new Dept();
				d.setCity(rs.getString("city"));
				d.setDid(rs.getInt("did"));
				d.setName(rs.getString("name"));
				depts.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return depts;
	}

	@Override
	public boolean insert(Dept d) {
		String sql = "INSERT INTO DEPT values(DEPARTMENT_SEQ.NEXTVAL"+","+"'"+d.getName()+"'"+","+"'"+d.getCity()+"')";
		System.out.println(sql);
		Connection conn = null;
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;	
	
	}

	@Override
	public boolean update(Dept d) {
		StringBuilder sb = new StringBuilder("UPDATE DEPT SET ");
		Field []fields = d.getClass().getDeclaredFields();
		for(int i=0;i<fields.length;i++){
			Object o;
			Field f = fields[i];
			try {
				f.setAccessible(true);
				o = f.get(d);
				if(o!=null&&!f.getName().equals("serialVersionUID")){
					String fieldName = f.getName();
					
					 if(!fieldName.equals("did")){
						sb.append(fieldName);
						sb.append("=");
						sb.append("'");
						sb.append(o);
						sb.append("'");
						sb.append(",");
					}
					
				}
			} catch (IllegalArgumentException | IllegalAccessException e) {
				e.printStackTrace();
			}
			
		}
		
		sb.deleteCharAt(sb.length()-1);
		
		sb.append(" WHERE DID = ").append(d.getDid());
		
		System.out.println(sb);
		
		Connection conn = null;		
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sb.toString());
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;
	}

	@Override
	public boolean delete(Dept d) {
		String sql = "DELETE DEPT WHERE DID = "+ d.getDid();
		Connection conn = null;
		PreparedStatement ps = null;
		int len = 0;
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			len = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, null);
		return len==0?false:true;
	}
	
	@Test
	public void test(){
//		Dept d = new Dept();
//		d.setDid(1);
//		d.setCity("HK");
//		
//		d.setName("EUC");
//		//this.delete(d);
//		this.insert(d);
//		this.delete(d);
//		
//		
//		
//		
		System.out.println(this.getAllPersonById(3));
	}

	@Override
	public List<Person> getAllPersonById(int did) {
		List<Person> persons = new ArrayList<Person>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM PERSON WHERE DID = "+did;
		System.out.println(sql);
		try {
			conn = DBUtil.getConnection();
			ps= conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){				
				Person p = new Person();
				p.setBirthday(rs.getDate("BIRTHDAY"));
				p.setTel(rs.getString("TEL"));
				p.setPid(rs.getInt("PID"));
				p.setName(rs.getString("NAME"));
				p.setDid(rs.getInt("DID"));
				p.setSalary(rs.getDouble("SALARY"));
				persons.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		DBUtil.free(conn, ps, rs);
		return persons;
	}
	
	
}
